﻿using System;

namespace SPDbFileExtractor.Framework.Code
{
    public static class SqlBuilder
    {
        /// <summary>
        /// Gets the version select command.
        /// </summary>
        /// <returns></returns>
        public static string GetVersionSelectCommand()
        {
            return @"
                        SELECT Version
                        FROM   [Versions]
                        WHERE  VersionId = '00000000-0000-0000-0000-000000000000';
                    ";
        }

        /// <summary>
        /// Gets the table validation command.
        /// </summary>
        /// <returns></returns>
        public static string GetTableValidationCommand()
        {
            return @"
                        SELECT TOP 1 * FROM  [Versions];
                        SELECT TOP 1 * FROM  [UserInfo];                        
                        SELECT TOP 1 * FROM  [Sites];
                        SELECT TOP 1 * FROM  [Webs];
                        SELECT TOP 1 * FROM  [AllLists];
                        SELECT TOP 1 * FROM  [AllDocs];
                        SELECT TOP 1 * FROM  [AllUserData];
                        SELECT TOP 1 * FROM  [AllDocStreams];
                    ";
        }

        /// <summary>
        /// Builds the select sites command.
        /// </summary>
        /// <returns></returns>
        public static string BuildSelectSitesCommand()
        {
            const string command = @"
                         SELECT DISTINCT
                                s.Id AS 'SiteId',
                                s.FullUrl AS 'FullUrl',
                                s.TimeCreated AS 'Created',
                                s.PortalName AS 'PortalName',
                                s.PortalURL AS 'PortalUrl',
                                s.HostHeader AS 'HostHeader',
                                ui.tp_Login AS 'Owner'
                         FROM   Sites AS s
                                LEFT OUTER JOIN
                                UserInfo AS ui
                                ON s.OwnerID = ui.tp_ID;
                 ";

            return command;
        }

        /// <summary>
        /// Builds the select webs command.
        /// </summary>
        /// <param name="siteId">The site id.</param>
        /// <returns></returns>
        public static string BuildSelectWebsCommand(Guid siteId)
        {
            const string command = @"
                             DECLARE @siteId AS UNIQUEIDENTIFIER = '%{siteId}%';
                             
                             SELECT DISTINCT
                                    w.Id AS 'WebId',
                                    w.SiteId AS 'SiteId',
                                    w.FullUrl AS 'FullUrl',
                                    w.ParentWebId AS 'ParentWebId',
                                    w.Title AS 'Title',
                                    w.TimeCreated AS 'Created',
                                    ui.tp_Login AS 'CreatedBy'
                             FROM   Webs AS w
                                    LEFT OUTER JOIN
                                    UserInfo AS ui
                                    ON w.Author = ui.tp_ID
                             WHERE  w.SiteId = @siteId;
                 ";

            return command.Replace("%{siteId}%", siteId.ToString());
        }

        /// <summary>
        /// Builds the select lists command.
        /// </summary>
        /// <param name="webId">The web id.</param>
        /// <returns></returns>
        public static string BuildSelectListsCommand(Guid webId)
        {
            const string command = @"
                             DECLARE @webId AS UNIQUEIDENTIFIER = '%{webId}%';
                             
                             SELECT DISTINCT
                                    lst.tp_ID AS 'ListId',
                                    lst.tp_WebId as 'WebId',
                                    lst.tp_Title AS 'Title',
                                    lst.tp_Description AS 'Description',
                                    tp_Created AS 'Created',
                                    ui.tp_Login AS 'CreatedBy',
                                    (SELECT COUNT(d.Id) from AllDocs d where d.ListId = lst.tp_Id and d.HasStream = 1) as 'ItemCount'
                             FROM   AllLists AS lst
                                    LEFT OUTER JOIN
                                    UserInfo AS ui
                                    ON lst.tp_Author = ui.tp_ID
                             WHERE  lst.tp_WebId = @webId
                                    AND lst.tp_BaseType = 1;
                 ";

            return command.Replace("%{webId}%", webId.ToString());
        }

        /// <summary>
        /// Builds the select items command.
        /// </summary>
        /// <param name="listId">The list id.</param>
        /// <param name="fileNameFilter"> </param>
        /// <param name="dirFilter"> </param>
        /// <returns></returns>
        public static string BuildSelectListItemsCommand(Guid listId, string fileNameFilter, string dirFilter)
        {
            const string command = @"
                                        DECLARE @listId AS UNIQUEIDENTIFIER = '%{listId}%';
                                        DECLARE @fileNameFilter AS VARCHAR (250) = %{fileNameFilter}%;
                                        DECLARE @dirFilter AS VARCHAR (250) = %{dirFilter}%;
                                        
                                        SELECT DISTINCT
                                               d.Id AS 'FileId',
                                               d.WebId AS 'WebId',
                                               d.SiteId AS 'SiteId',
                                               d.DirName AS 'DirName',
                                               d.LeafName AS 'FileName',
                                               d.Size AS 'Size',
                                               d.UIVersion AS 'UIVersion',
                                               d.InternalVersion AS 'InternalVersion',
                                               d.ETagVersion AS 'ETagVersion',
                                               aud.tp_Created AS 'Created',
                                               ui.tp_Login AS 'CreatedBy'
                                        FROM   AllDocs AS d
                                               INNER JOIN
                                               AllUserData AS aud
                                               ON d.Id = aud.tp_DocId
                                               INNER JOIN
                                               UserInfo AS ui
                                               ON aud.tp_Author = ui.tp_ID
                                        WHERE  d.ListId = @listId
                                               AND d.HasStream = 1
                                               AND (@fileNameFilter IS NULL
                                                    OR d.LeafName LIKE '%' + @fileNameFilter + '%')
                                               AND (@dirFilter IS NULL
                                                    OR d.DirName LIKE '%' + @dirFilter + '%');                               
                 ";

            var tmp = command.Replace("%{listId}%", listId.ToString());
            tmp = tmp.Replace("%{fileNameFilter}%", fileNameFilter == null || fileNameFilter.Trim().Length == 0 ? "NULL" : "'" + fileNameFilter + "'");
            tmp = tmp.Replace("%{dirFilter}%", dirFilter == null || dirFilter.Trim().Length == 0 ? "NULL" : "'" + dirFilter + "'");
            return tmp;
        }

        /// <summary>
        /// Builds the select item versions command.
        /// </summary>
        /// <param name="itemId">The item id.</param>
        /// <returns></returns>
        public static string BuildSelectItemVersionsCommand(Guid itemId)
        {
            const string command = @"
                                DECLARE @itemId AS UNIQUEIDENTIFIER = '%{itemId}%';
                                
                                DECLARE @itemVersions TABLE (
                                    DocId           UNIQUEIDENTIFIER,
                                    UIVersion       INT             ,
                                    InternalVersion INT             ,
                                    Size            INT             );
                                
                                INSERT INTO @itemVersions
                                SELECT adv.Id,
                                       adv.UIVersion,
                                       adv.InternalVersion,
                                       adv.Size
                                FROM   AllDocVersions AS adv
                                WHERE  adv.Id = @itemId;
                                
                                INSERT INTO @itemVersions
                                SELECT ad.Id,
                                       ad.UIVersion,
                                       ad.InternalVersion,
                                       ad.Size
                                FROM   AllDocs AS ad
                                WHERE  ad.Id = @itemId;
                                
                                SELECT   DISTINCT
                                         aud.tp_DocId AS 'FileId',
                                         aud.tp_Version AS 'ETagVersion',
                                         aud.tp_UIVersion AS 'UIVersion',
                                         iv.InternalVersion as 'InternalVersion',
                                         ad.LeafName AS 'FileName',
                                         ad.DirName AS 'DirName',
                                         aud.tp_Modified AS 'Created',
                                         ui.tp_Login AS 'CreatedBy',
                                         iv.Size AS 'Size'
                                FROM     AllUserData AS aud
                                         INNER JOIN
                                         @itemVersions AS iv
                                         ON aud.tp_DocId = iv.DocId
                                            AND aud.tp_UIVersion = iv.UIVersion
                                         INNER JOIN
                                         AllDocs AS ad
                                         ON aud.tp_DocId = ad.Id
                                         INNER JOIN
                                         UserInfo AS ui
                                         ON aud.tp_Editor = ui.tp_ID
                                ORDER BY aud.tp_UIVersion DESC;
                 ";

            return command.Replace("%{itemId}%", itemId.ToString());
        }

        /// <summary>
        /// Builds the select item content command.
        /// </summary>
        /// <param name="itemId">The item id.</param>
        /// <param name="internalVersion">The internal version.</param>
        /// <returns></returns>
        public static string BuildSelectItemContentCommand(Guid itemId, int internalVersion)
        {
            const string command = @"
                                  DECLARE @itemId AS UNIQUEIDENTIFIER = '%{itemId}%';
                                  DECLARE @internalVersion AS INT = %{internalVersion}%;
                                  
                                  SELECT DISTINCT
                                         st.Id AS 'FileId',
                                         st.internalVersion AS 'internalVersion',
                                         st.Content AS 'Content'
                                  FROM   AllDocStreams AS st
                                  WHERE  st.Id = @itemId
                                         AND st.internalVersion = @internalVersion;                                  
                 ";

            var tmp = command.Replace("%{itemId}%", itemId.ToString());
            return tmp.Replace("%{internalVersion}%", internalVersion.ToString());
        }
    }
}
